<?php
class ASmsPartner extends CActiveRecord
{
    public function getAllserviceNumber()
    {
        $sql = "SELECT DISTINCT(serviceNumber) FROM c_sms_log ORDER BY serviceNumber";
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();
        return $rows;
    }
    
    public function GetPartner($search, $currentPage, $numberRecordPerPage)
    {
        $search["fromDate"] .= " 23:59:59";
        $startRecord = ($currentPage - 1) * $numberRecordPerPage;
        
        $condition = "";        
        $condition .= " AND create_date>='" . strtotime($search["toDate"]) . "'";
        $condition .= " AND create_date<='" . strtotime($search["fromDate"]) . "'";        
        $condition .= !empty($search["telco"]) ? " AND telco=" . mysql_escape_string($search["telco"]) : "";        
        $condition .= !empty($search["serviceNumber"]) ? " AND serviceNumber=" . mysql_escape_string($search["serviceNumber"]) : "";        
        $condition .= !empty($search["price"]) ? " AND price=" . mysql_escape_string($search["price"]) : "";        
        $condition .= " AND status=1";        
        
        if(!empty($search["keyword"])){
            $sql = "SELECT id FROM c_partner WHERE name='" . mysql_escape_string($search["keyword"]) . "'";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryRow();    
            
            $condition .= empty($rows) ? " AND partnerId=0" : " AND partnerId=" . $rows["id"];
        }
          
        /* Tiêu chí sắp xếp mặc định */      
        $sql = "SELECT partnerId, sum(price) as total_price FROM c_sms_log WHERE 1 " . $condition . " GROUP BY partnerId ORDER BY total_price DESC LIMIT " . $startRecord . ", " . $numberRecordPerPage;        
        
        if($search["order"]=="1") {
            /* Sắp xếp theo doanh thu từ thấp đến cao */
            $sql = "SELECT partnerId, sum(price) as total_price FROM c_sms_log WHERE 1 " . $condition . " GROUP BY partnerId ORDER BY total_price ASC LIMIT " . $startRecord . ", " . $numberRecordPerPage;        
        } else if($search["order"]=="2") {
            /* Sắp xếp theo doanh thu từ cao đến thấp */
            $sql = "SELECT partnerId, sum(price) as total_price FROM c_sms_log WHERE 1 " . $condition . " GROUP BY partnerId ORDER BY total_price DESC LIMIT " . $startRecord . ", " . $numberRecordPerPage;        
        } else if($search["order"]=="3") {
            /* Sắp xếp theo sản lượng từ thấp đến cao */
            $sql = "SELECT partnerId, count(id) as total_sms FROM c_sms_log WHERE 1 " . $condition . " GROUP BY partnerId ORDER BY total_sms ASC LIMIT " . $startRecord . ", " . $numberRecordPerPage;        
        } else if($search["order"]=="4") {
            /* Sắp xếp theo sản lượng từ cao đến thấp */
            $sql = "SELECT partnerId, count(id) as total_sms FROM c_sms_log WHERE 1 " . $condition . " GROUP BY partnerId ORDER BY total_sms DESC LIMIT " . $startRecord . ", " . $numberRecordPerPage;        
        }  
        
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();    
        return $rows;
    }
    
    public function GetTotalPartner($search)
    {
        $search["fromDate"] .= " 23:59:59";
        $condition = "";        
        $condition .= " AND create_date>='" . strtotime($search["toDate"]) . "'";
        $condition .= " AND create_date<='" . strtotime($search["fromDate"]) . "'";        
        $condition .= !empty($search["telco"]) ? " AND telco=" . mysql_escape_string($search["telco"]) : "";        
        $condition .= !empty($search["serviceNumber"]) ? " AND serviceNumber=" . mysql_escape_string($search["serviceNumber"]) : "";        
        $condition .= !empty($search["price"]) ? " AND price=" . mysql_escape_string($search["price"]) : "";        
        $condition .= " AND status=1";
        
        if(!empty($search["keyword"])){
            $sql = "SELECT id FROM c_partner WHERE name='" . mysql_escape_string($search["keyword"]) . "'";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryRow();    
            
            $condition .= empty($rows) ? " AND partnerId=0" : " AND partnerId=" . $rows["id"];
        }
                
        $sql = "SELECT count(DISTINCT partnerId) FROM c_sms_log WHERE 1 " . $condition;                        
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryRow();    
    
        return $rows["count(DISTINCT partnerId)"];    
    }
    
    public function getReport($search, $rate, $rate_sms)
    {
        $search["fromDate"] .= " 23:59:59";
        $condition = "";
        $condition .= $search["list_partner_id"]!="" ? " AND partnerId IN (" . mysql_escape_string($search["list_partner_id"]) . ")" : "";
        $condition .= " AND create_date>='" . strtotime($search["toDate"]) . "'";
        $condition .= " AND create_date<='" . strtotime($search["fromDate"]) . "'";        
        $condition .= !empty($search["telco"]) ? " AND telco=" . mysql_escape_string($search["telco"]) : "";        
        $condition .= !empty($search["serviceNumber"]) ? " AND serviceNumber=" . mysql_escape_string($search["serviceNumber"]) : "";        
        $condition .= !empty($search["price"]) ? " AND price=" . mysql_escape_string($search["price"]) : "";        
        
        $sql = "SELECT count(id), partnerId, price, month, year, type, serviceNumber FROM c_sms_log WHERE status=1 AND type!=1 " . $condition . " GROUP BY partnerId, month, year, price, type, serviceNumber";        
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();
                
        foreach($rows as $row){
            
            $id = $row["partnerId"];            
            $index_rate = $row["month"] . "_" . $row["year"];
            $sc = preg_replace("/(\d+)(\d+)(\d+)(\d+)/si", "X$2XX", $row["serviceNumber"]);
            $type = $row["type"];
            
            if(!isset($sms[$id]["total_sms"])) $sms[$id]["total_sms"] = 0;
            if(!isset($sms[$id]["5000"])) $sms[$id]["5000"] = 0;
            if(!isset($sms[$id]["10000"])) $sms[$id]["10000"] = 0;
            if(!isset($sms[$id]["15000"])) $sms[$id]["15000"] = 0;
            if(!isset($sms[$id]["telco"])) $sms[$id]["telco"] = 0;
            if(!isset($sms[$id]["serviceNumber"])) $sms[$id]["serviceNumber"] = 0;
            if(!isset($sms[$id]["wapmaster"])) $sms[$id]["wapmaster"] = 0;
            if(!isset($sms[$id]["mtop"])) $sms[$id]["mtop"] = 0;
            if(!isset($sms[$id]["partner"])) $sms[$id]["partner"] = 0;
            
            $sms[$id]["total_sms"] += $row["count(id)"];
            $sms[$id]["5000"] += $row["price"]==5000 ? $row["count(id)"] : 0;
            $sms[$id]["10000"] += $row["price"]==10000 ? $row["count(id)"] : 0;
            $sms[$id]["15000"] += $row["price"]==15000 ? $row["count(id)"] : 0;
            
            $total_money = $row["price"] * $row["count(id)"];
            
            $rateSms = $rate_sms[$index_rate][$sc] / 100;
            $sms[$id]["telco"] += $total_money * $rateSms;  
            $sms[$id]["serviceNumber"] += $total_money * $rateSms * 0.85;  
                        
            $sms[$id]["mtop"] += $total_money * $rateSms * 0.85 * ($rate[$index_rate][$type]["percent_mtop"] / 100);
            $sms[$id]["wapmaster"] += $total_money * $rateSms * 0.85 * ($rate[$index_rate][$type]["percent_wapmaster"] / 100);
        }
        
        $sql = "SELECT count(id), partnerId, price, month, year, dataId, percent_wapmaster, percent_partner, serviceNumber FROM c_sms_log WHERE status=1 AND type=1 " . $condition . " GROUP BY partnerId, dataId, price, percent_wapmaster, percent_partner, serviceNumber, month, year";
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();
       
        foreach($rows as $row){
            $id = $row["partnerId"];                        
            
            $index = $row["month"] . "_" . $row["year"];
            $sc = preg_replace("/(\d+)(\d+)(\d+)(\d+)/si", "X$2XX", $row["serviceNumber"]);
            
            if(!isset($sms[$id]["total_sms"])) $sms[$id]["total_sms"] = 0;
            if(!isset($sms[$id]["5000"])) $sms[$id]["5000"] = 0;
            if(!isset($sms[$id]["10000"])) $sms[$id]["10000"] = 0;
            if(!isset($sms[$id]["15000"])) $sms[$id]["15000"] = 0;
            if(!isset($sms[$id]["telco"])) $sms[$id]["telco"] = 0;
            if(!isset($sms[$id]["serviceNumber"])) $sms[$id]["serviceNumber"] = 0;
            if(!isset($sms[$id]["wapmaster"])) $sms[$id]["wapmaster"] = 0;
            if(!isset($sms[$id]["mtop"])) $sms[$id]["mtop"] = 0;
            if(!isset($sms[$id]["partner"])) $sms[$id]["partner"] = 0;
            
            $sms[$id]["total_sms"] += $row["count(id)"];
            $sms[$id]["5000"] += $row["price"]==5000 ? $row["count(id)"] : 0;
            $sms[$id]["10000"] += $row["price"]==10000 ? $row["count(id)"] : 0;
            $sms[$id]["15000"] += $row["price"]==15000 ? $row["count(id)"] : 0;
            
            $total_money = $row["price"] * $row["count(id)"];
            
            $rateSms = $rate_sms[$index][$sc] / 100;
            $sms[$id]["telco"] += $total_money * $rateSms;  
            $sms[$id]["serviceNumber"] += $total_money * $rateSms * 0.85;  
                
            $sms[$id]["wapmaster"] += $total_money * $rateSms * 0.85 * ($row["percent_wapmaster"] / 100);
            $sms[$id]["partner"] += $total_money * $rateSms * 0.85 * ($row["percent_partner"] / 100);
            $sms[$id]["mtop"] += $total_money * $rateSms * 0.85 * ((100 - $row["percent_wapmaster"] - $row["percent_partner"]) / 100);
        }
        
        $sql = "SELECT * FROM c_partner WHERE id IN (" . $search["list_partner_id"] . ")";
        $command = Yii::app()->db->createCommand($sql);
        $rowsPartner = $command->queryAll();
        $replacePartner = array();
        foreach($rowsPartner as $row){
            $replacePartner[$row["id"]]["id"] = $row["id"];
            $replacePartner[$row["id"]]["name"] = $row["name"];
        }
                
        foreach($sms as $i=>$row){
            $sms[$i]["name"] = isset($replacePartner[$i]["name"]) ? $replacePartner[$i]["name"] : "";            
        }
                  
        $partners = explode(", ", $search["list_partner_id"]);
        $response = array();
        foreach($partners as $id){
            if(isset($sms[$id])){
                $response[$id] = $sms[$id];
            }   
        }
        
        return $response;
    }
    
    public function getReportByPartner($search, $rate, $rate_sms, $currentPage, $numberRecordPerPage)
    {
        $search["fromDate"] .= " 23:59:59";
        $startRecord = ($currentPage - 1) * $numberRecordPerPage;
        
        $condition = "";
        $condition .= " AND partnerId=" . $search["partnerId"];
        $condition .= " AND create_date>='" . strtotime($search["toDate"]) . "'";
        $condition .= " AND create_date<='" . strtotime($search["fromDate"]) . "'";        
        $condition .= !empty($search["telco"]) ? " AND telco=" . mysql_escape_string($search["telco"]) : "";        
        $condition .= !empty($search["serviceNumber"]) ? " AND serviceNumber=" . mysql_escape_string($search["serviceNumber"]) : "";        
        $condition .= !empty($search["price"]) ? " AND price=" . mysql_escape_string($search["price"]) : "";                
        $condition .= " AND status=1";                
        
        if(!empty($search["keyword"])){
             
            if($search["type"]==1){
                $sql = "SELECT id FROM c_partner WHERE name='" . mysql_escape_string($search["keyword"]) . "'";
                $command = Yii::app()->db->createCommand($sql);
                $rows = $command->queryRow();    
                
                $condition .= empty($rows) ? " AND partnerId=0" : " AND partnerId=" . $rows["id"];
            }
            
            if($search["type"]==2){
                $sql = "SELECT id FROM c_game WHERE title='" . mysql_escape_string($search["keyword"]) . "'";
                $command = Yii::app()->db->createCommand($sql);
                $rows = $command->queryRow();    
                
                $condition .= empty($rows) ? " AND 0" : " AND dataId=" . $rows["id"] . " AND type=1";
            } 
            if($search["type"]==3){
                $sql = "SELECT id FROM c_video WHERE title='" . mysql_escape_string($search["keyword"]) . "'";
                $command = Yii::app()->db->createCommand($sql);
                $rows = $command->queryRow();    
                
                $condition .= empty($rows) ? " AND 0" : " AND dataId=" . $rows["id"] . " AND type=2";
            }
            
            if($search["type"]==4){
                $sql = "SELECT id FROM c_album WHERE title='" . mysql_escape_string($search["keyword"]) . "'";
                $command = Yii::app()->db->createCommand($sql);
                $rows = $command->queryRow();    
                
                $condition .= empty($rows) ? " AND 0" : " AND dataId=" . $rows["id"] . " AND type=3";
            }
            
            if($search["type"]==5){
                $sql = "SELECT id FROM c_ring_back WHERE title='" . mysql_escape_string($search["keyword"]) . "'";
                $command = Yii::app()->db->createCommand($sql);
                $rows = $command->queryRow();    
                
                $condition .= empty($rows) ? " AND 0" : " AND dataId=" . $rows["id"] . " AND type=4";
            }
            
            if($search["type"]==6){
                /* Xổ số */                
            }
            
            if($search["type"]==7){
                $sql = "SELECT id FROM c_sms_kute WHERE title='" . mysql_escape_string($search["keyword"]) . "'";
                $command = Yii::app()->db->createCommand($sql);
                $rows = $command->queryAll();    
                
                $list_id = "0";
                foreach($rows as $row){
                    $list_id .= "," . $row["id"];    
                }
                
                $condition .= empty($rows) ? " AND 0" : " AND dataId IN (" . $list_id . ") AND type=6";
            }
        }
        
        $sql = "SELECT * FROM c_sms_log WHERE 1 " . $condition . " ORDER BY create_date DESC LIMIT " . $startRecord . ", " . $numberRecordPerPage;
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();
                
        $list_partner_id = "0";
        $list_game_id = "0";
        $list_video_id = "0";
        $list_image_id = "0";
        $list_style_id = "0";
        $list_sms_id = "0";
        foreach($rows as $row){
            $list_partner_id .= "," . $row["partnerId"];
            $list_game_id .= $row["type"]=="1" ? "," . $row["dataId"] : "";
            $list_video_id .= $row["type"]=="2" ? "," . $row["dataId"] : "";
            $list_image_id .= $row["type"]=="3" ? "," . $row["dataId"] : "";
            $list_style_id .= $row["type"]=="4" ? "," . $row["dataId"] : "";
            $list_sms_id .= $row["type"]=="6" ? "," . $row["dataId"] : "";
        }
        
        $list_partner_id = ltrim($list_partner_id, ",");
        $list_game_id = ltrim($list_game_id, ",");
        $list_video_id = ltrim($list_video_id, ",");
        $list_image_id = ltrim($list_image_id, ",");
        $list_style_id = ltrim($list_style_id, ",");
        $list_sms_id = ltrim($list_sms_id, ",");
        
        $list_partner_id = Common::remove_duplicate($list_partner_id);
        $list_game_id = Common::remove_duplicate($list_game_id);
        $list_video_id = Common::remove_duplicate($list_video_id);
        $list_image_id = Common::remove_duplicate($list_image_id);
        $list_style_id = Common::remove_duplicate($list_style_id);
        $list_sms_id = Common::remove_duplicate($list_sms_id);        
        
        if(!empty($list_game_id)){
            $sql = "SELECT id, title, isOnline, isSms FROM c_game WHERE id IN (" . $list_game_id . ")";
            $command = Yii::app()->db->createCommand($sql);
            $rowsGame = $command->queryAll();
            $replaceGame = array();
            
            foreach($rowsGame as $row){
                $replaceGame[$row["id"]]["title"] = $row["title"];
                $replaceGame[$row["id"]]["isOnline"] = $row["isOnline"];
                $replaceGame[$row["id"]]["isSms"] = $row["isSms"];
            }
        }
        
        if(!empty($list_video_id)){
            $sql = "SELECT id, title FROM c_video WHERE id IN (" . $list_video_id . ")";
            $command = Yii::app()->db->createCommand($sql);
            $rowsVideo = $command->queryAll();
            $replaceVideo = array();
            
            foreach($rowsVideo as $row){
                $replaceVideo[$row["id"]]["title"] = $row["title"];                
            }
        }
        
        if(!empty($list_image_id)){
            $sql = "SELECT id, title FROM c_album WHERE id IN (" . $list_image_id . ")";
            $command = Yii::app()->db->createCommand($sql);
            $rowsImage = $command->queryAll();
            $replaceImage = array();
            
            foreach($rowsImage as $row){
                $replaceImage[$row["id"]]["title"] = $row["title"];                
            }
        }
        
        if(!empty($list_style_id)){
            $sql = "SELECT id, title FROM c_ring_back WHERE id IN (" . $list_style_id . ")";
            $command = Yii::app()->db->createCommand($sql);
            $rowsStyle = $command->queryAll();
            $replaceStyle = array();
            
            foreach($rowsStyle as $row){
                $replaceStyle[$row["id"]]["title"] = $row["title"];                
            }
        }
        
        if(!empty($list_sms_id)){
            $sql = "SELECT id, title FROM c_sms_kute WHERE id IN (" . $list_sms_id . ")";
            $command = Yii::app()->db->createCommand($sql);
            $rowsSms = $command->queryAll();
            $replaceSms = array();
            
            foreach($rowsSms as $row){
                $replaceSms[$row["id"]]["title"] = $row["title"];                
            }
        }

        $i = 0;
        while(isset($rows[$i])){
            $type = $rows[$i]["type"];
            $dataId = $rows[$i]["dataId"];            
            
            if($type=="1"){
                $rows[$i]["title"] = isset($replaceGame[$dataId]["title"]) ? $replaceGame[$dataId]["title"] : "Không xác định";
            } else if($type=="2"){
                $rows[$i]["title"] = isset($replaceVideo[$dataId]["title"]) ? $replaceVideo[$dataId]["title"] : "Không xác định";    
            } else if($type=="3"){
                $rows[$i]["title"] = isset($replaceImage[$dataId]["title"]) ? $replaceImage[$dataId]["title"] : "Không xác định";    
            } else if($type=="4"){
                $rows[$i]["title"] = isset($replaceStyle[$dataId]["title"]) ? $replaceStyle[$dataId]["title"] : "Không xác định";    
            } else if($type=="6"){
                $rows[$i]["title"] = isset($replaceSms[$dataId]["title"]) ? $replaceSms[$dataId]["title"] : "Không xác định";    
            } else if($type=="7"){
                $rows[$i]["title"] = "Test";                    
            } else if($type=="8"){
                $rows[$i]["title"] = "Mod Game";                    
            }
            
            $rows[$i]["title"] = isset($rows[$i]["title"]) ? $rows[$i]["title"] : "Không xác định";
            $rows[$i]["content_type"] = isset(LoadConfig::$content[$type]) ? LoadConfig::$content[$type] : "Không xác định";                
            
            $index_rate = $rows[$i]["month"] . "_" . $rows[$i]["year"];
            $sc = preg_replace("/(\d+)(\d+)(\d+)(\d+)/si", "X$2XX", $rows[$i]["serviceNumber"]);
            $rateSms = $rate_sms[$index_rate][$sc] / 100;
            
            $rows[$i]["telco"] = $rows[$i]["price"] * $rateSms;  
            $rows[$i]["sc"] = $rows[$i]["price"] * $rateSms * 0.85;  
            if($rows[$i]["type"]=="1"){
                $rows[$i]["wapmaster"] = $rows[$i]["sc"] * ($rows[$i]["percent_wapmaster"] / 100);
                $rows[$i]["partner"] = $rows[$i]["sc"] * ($rows[$i]["percent_partner"] / 100);
                $rows[$i]["mtop"] = $rows[$i]["sc"] * ((100 - $rows[$i]["percent_wapmaster"] - $rows[$i]["percent_partner"]) / 100);
            } else {                
                $rows[$i]["wapmaster"] = $rows[$i]["sc"] * ($rate[$index_rate][$type]["percent_wapmaster"] / 100);
                $rows[$i]["partner"] = 0;
                $rows[$i]["mtop"] = $rows[$i]["sc"] * ($rate[$index_rate][$type]["percent_mtop"] / 100);                 
            }
            
            $i++;
        }
        
        return $rows;
    }
    
    public function getTotalReportByPartner($search)
    {
        $search["fromDate"] .= " 23:59:59";
        $condition = "";
        $condition .= " AND partnerId=" . $search["partnerId"];
        $condition .= " AND create_date>='" . strtotime($search["toDate"]) . "'";
        $condition .= " AND create_date<='" . strtotime($search["fromDate"]) . "'";        
        $condition .= !empty($search["telco"]) ? " AND telco=" . mysql_escape_string($search["telco"]) : "";        
        $condition .= !empty($search["serviceNumber"]) ? " AND serviceNumber=" . mysql_escape_string($search["serviceNumber"]) : "";        
        $condition .= !empty($search["price"]) ? " AND price=" . mysql_escape_string($search["price"]) : "";                
        $condition .= " AND status=1";                
        
        if(!empty($search["keyword"])){
             
            if($search["type"]==1){
                $sql = "SELECT id FROM c_partner WHERE name='" . mysql_escape_string($search["keyword"]) . "'";
                $command = Yii::app()->db->createCommand($sql);
                $rows = $command->queryRow();    
                
                $condition .= empty($rows) ? " AND partnerId=0" : " AND partnerId=" . $rows["id"];
            }
            
            if($search["type"]==2){
                $sql = "SELECT id FROM c_game WHERE title='" . mysql_escape_string($search["keyword"]) . "'";
                $command = Yii::app()->db->createCommand($sql);
                $rows = $command->queryRow();    
                
                $condition .= empty($rows) ? " AND 0" : " AND dataId=" . $rows["id"] . " AND type=1";
            } 
            if($search["type"]==3){
                $sql = "SELECT id FROM c_video WHERE title='" . mysql_escape_string($search["keyword"]) . "'";
                $command = Yii::app()->db->createCommand($sql);
                $rows = $command->queryRow();    
                
                $condition .= empty($rows) ? " AND 0" : " AND dataId=" . $rows["id"] . " AND type=2";
            }
            
            if($search["type"]==4){
                $sql = "SELECT id FROM c_album WHERE title='" . mysql_escape_string($search["keyword"]) . "'";
                $command = Yii::app()->db->createCommand($sql);
                $rows = $command->queryRow();    
                
                $condition .= empty($rows) ? " AND 0" : " AND dataId=" . $rows["id"] . " AND type=3";
            }
            
            if($search["type"]==5){
                $sql = "SELECT id FROM c_ring_back WHERE title='" . mysql_escape_string($search["keyword"]) . "'";
                $command = Yii::app()->db->createCommand($sql);
                $rows = $command->queryRow();    
                
                $condition .= empty($rows) ? " AND 0" : " AND dataId=" . $rows["id"] . " AND type=4";
            }
            
            if($search["type"]==6){
                /* Xổ số */                
            }
            
            if($search["type"]==7){
                $sql = "SELECT id FROM c_sms_kute WHERE title='" . mysql_escape_string($search["keyword"]) . "'";
                $command = Yii::app()->db->createCommand($sql);
                $rows = $command->queryAll();    
                
                $list_id = "0";
                foreach($rows as $row){
                    $list_id .= "," . $row["id"];    
                }
                
                $condition .= empty($rows) ? " AND 0" : " AND dataId IN (" . $list_id . ") AND type=6";
            }
        }
        
        $sql = "SELECT count(id) FROM c_sms_log WHERE 1 " . $condition;
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryRow();        
        return $rows["count(id)"];
    }
    
    public function getReportExport($search, $rate)
    {
        $sms = array();
        
        $search["fromDate"] .= " 23:59:59";
        $condition = "";
        $condition .= " AND partnerId=" . $search["partnerId"];
        $condition .= " AND create_date>='" . strtotime($search["toDate"]) . "'";
        $condition .= " AND create_date<='" . strtotime($search["fromDate"]) . "'";                
               
        $sql = "SELECT count(id), serviceNumber, price, dataId, percent_wapmaster, percent_partner, month, year FROM c_sms_log WHERE status=1 AND type=1 " . $condition . " GROUP BY month, year, dataId, serviceNumber, percent_wapmaster, percent_partner";        
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();
       
        $list_data_id = "";
       
        foreach($rows as $row){
            $id = $row["dataId"];
            $list_data_id .= "," . $id;
            
            $serviceNumber = $row["serviceNumber"];                                                
            $month = $row["month"];
            $year = $row["year"];
            
            if(!isset($sms[$id][$serviceNumber]["total_sms"])) $sms[$id][$serviceNumber]["total_sms"] = 0;
            if(!isset($sms[$id][$serviceNumber]["telco"])) $sms[$id][$serviceNumber]["telco"] = 0;
            if(!isset($sms[$id][$serviceNumber]["serviceNumber"])) $sms[$id][$serviceNumber]["serviceNumber"] = 0;
            if(!isset($sms[$id][$serviceNumber]["wapmaster"])) $sms[$id][$serviceNumber]["wapmaster"] = 0;
            if(!isset($sms[$id][$serviceNumber]["mtop"])) $sms[$id][$serviceNumber]["mtop"] = 0;
            if(!isset($sms[$id][$serviceNumber]["partner"])) $sms[$id][$serviceNumber]["partner"] = 0;
            
           
            $sms[$id][$serviceNumber]["total_sms"] += $row["count(id)"];            
            
            $total_money = $row["price"] * $row["count(id)"];
            
            $sc = preg_replace("/^(\d+)(\d+)(\d+)(\d+)$/si", "X$2XX", $serviceNumber);            
            $rate_telco = $rate[$month . "_" . $year][$sc] / 100;
            
            $sms[$id][$serviceNumber]["telco"] += $total_money * $rate_telco;  
            $sms[$id][$serviceNumber]["serviceNumber"] += $total_money * $rate_telco * 0.85;  
            $sms[$id][$serviceNumber]["wapmaster"] += $total_money * $rate_telco * 0.85 * ($row["percent_wapmaster"] / 100);
            $sms[$id][$serviceNumber]["partner"] += $total_money * $rate_telco * 0.85 * ($row["percent_partner"] / 100);
            $sms[$id][$serviceNumber]["mtop"] += $total_money * $rate_telco * 0.85 * ((100 - $row["percent_wapmaster"] - $row["percent_partner"]) / 100);
        }
        
        if(!empty($list_data_id)){
            $list_data_id = ltrim($list_data_id, ",");
            $sql = "SELECT id, title FROM c_game WHERE id IN (" . $list_data_id . ")";
            $command = Yii::app()->db->createCommand($sql);
            $rowsGame = $command->queryAll();
            $replaceGame = array();
            
            foreach($rowsGame as $row){
                $replaceGame[$row["id"]]["title"] = $row["title"];                
            }
        }
        
        foreach($sms as $i=>$row){
            $sms[$i]["title"] = isset($replaceGame[$i]["title"]) ? $replaceGame[$i]["title"] : "Không xác định";
        }    
        
        return $sms;      
    }
}  
?>